﻿//using Microsoft.Office.Interop.Word;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
/* OpenXml */
using System.IO;
using System.Xml;
using OfficeOpenXml;
using System.Drawing;
using OfficeOpenXml.Style;
using Novacode;
/* OpenXml */


namespace KPIS.GERP.ESS.APP.App_UI.Commons
{
    public class MailMerge
    {
        //public bool MergeTemplate(string strTemplateFilePath, string strSaveAsFilePath
        //    , Dictionary<string, string> fields
        //    , Dictionary<string, System.Data.DataTable> dtbs = null
        //    , List<Dictionary<string, string>> Dics = null
        //    , Dictionary<string, bool> areas = null)
        //{
        //    bool bOK = false;

        //    using (DocX document = DocX.Load(strTemplateFilePath))
        //    {
        //        Object oMissing = System.Reflection.Missing.Value;
        //        Object oTemplatePath = strTemplateFilePath;
        //        Application application = new Application();
        //        Microsoft.Office.Interop.Word._Document wordDoc = new Document();

        //        try
        //        {
        //            wordDoc = application.Documents.Add(ref oTemplatePath, ref oMissing, ref oMissing, ref oMissing);

        //            if (fields != null)
        //            {
        //                //foreach (var field in fields)
        //                //{
        //                //    document.ReplaceText("«" + field.Key + "»", field.Value);
        //                //}


        //                foreach (Field myMergeField in wordDoc.Fields)
        //                {
        //                    Range rngFieldCode = myMergeField.Code;
        //                    String fieldText = rngFieldCode.Text;

        //                    if (fieldText.StartsWith(" MERGEFIELD"))
        //                    {
        //                        Int32 endMerge = fieldText.IndexOf("\\");
        //                        Int32 fieldNameLength = fieldText.Length - endMerge;
        //                        String fieldName = fieldText.Substring(11, endMerge - 11);

        //                        fieldName = fieldName.Trim();

        //                        if (fields.ContainsKey(fieldName))
        //                        {
        //                            string strValue = fields[fieldName];
        //                            myMergeField.Select();
        //                            application.Selection.TypeText(strValue);
        //                        }
        //                    }
        //                }
        //            }

        //            if (dtbs != null)
        //            {
        //                foreach (var _dtb in dtbs)
        //                {
        //                    System.Data.DataTable dtb = _dtb.Value;
        //                    for (int i = 0; i < dtb.Rows.Count; i++)
        //                    {
        //                        System.Data.DataRow dr = dtb.Rows[i];

        //                        foreach (System.Data.DataColumn col in dtb.Columns)
        //                        {
        //                            var ColumnName = col.ColumnName;// + (i + 1).ToString();
        //                            string strValue = dr[ColumnName].ToString();

        //                            //document.ReplaceText("«" + ColumnName + "»", strValue);

        //                            foreach (Field myMergeField in wordDoc.Fields)
        //                            {
        //                                Range rngFieldCode = myMergeField.Code;
        //                                String fieldText = rngFieldCode.Text;

        //                                if (fieldText.StartsWith(" MERGEFIELD"))
        //                                {
        //                                    Int32 endMerge = fieldText.IndexOf("\\");
        //                                    Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                    String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                    fieldName = fieldName.Trim();

        //                                    if (ColumnName + (i + 1).ToString() == fieldName)
        //                                    {
        //                                        myMergeField.Select();
        //                                        application.Selection.TypeText(strValue);
        //                                    }
        //                                }
        //                            }
        //                        }
        //                    }

        //                    foreach (System.Data.DataColumn col in dtb.Columns)
        //                    {
        //                        var ColumnName = col.ColumnName;

        //                        //document.ReplaceText("«" + ColumnName + "»", "");

        //                        foreach (Field myMergeField in wordDoc.Fields)
        //                        {
        //                            Range rngFieldCode = myMergeField.Code;
        //                            String fieldText = rngFieldCode.Text;

        //                            if (fieldText.StartsWith(" MERGEFIELD"))
        //                            {
        //                                Int32 endMerge = fieldText.IndexOf("\\");
        //                                Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                fieldName = fieldName.Trim();

        //                                if (fieldName.Contains(ColumnName))
        //                                {
        //                                    myMergeField.Select();

        //                                    if (application.Selection.Text == "«" + fieldName + "»")
        //                                        application.Selection.TypeText(" ");
        //                                }
        //                            }
        //                        }
        //                    }

        //                    //object oEndOfDoc = _dtb.Key;
        //                    //System.Data.DataTable dtb = _dtb.Value;
        //                    //Range wrdRng = wordDoc.Bookmarks[ref oEndOfDoc].Range;//.get_Item(ref oEndOfDoc).Range;
        //                    //var oTable = wordDoc.Tables.Add(wrdRng, dtb.Rows.Count, dtb.Columns.Count, ref oMissing, ref oMissing);
        //                    //oTable.Range.ParagraphFormat.SpaceAfter = 6;
        //                    ////int r, c;
        //                    //string strText;
        //                    //for (int r = 0; r < dtb.Rows.Count; r++)
        //                    //{
        //                    //    for (int c = 0; c < dtb.Columns.Count; c++)
        //                    //    {
        //                    //        strText = dtb.Rows[r][c].ToString();
        //                    //        oTable.Cell(r + 1, c + 1).Range.Text = strText;
        //                    //    }
        //                    //}
        //                }
        //            }

        //            if (Dics != null && Dics.Count > 0)
        //            {
        //                for (int i = 0; i < Dics.Count; i++)
        //                {
        //                    var Dic = Dics[i];

        //                    foreach (var DicItem in Dic)
        //                    {
        //                        string ColumnName = DicItem.Key;// + (i + 1).ToString();
        //                        string strValue = DicItem.Value;

        //                        //document.ReplaceText("«" + ColumnName + "»", strValue);
        //                        foreach (Field myMergeField in wordDoc.Fields)
        //                        {
        //                            Range rngFieldCode = myMergeField.Code;
        //                            String fieldText = rngFieldCode.Text;

        //                            if (fieldText.StartsWith(" MERGEFIELD"))
        //                            {
        //                                Int32 endMerge = fieldText.IndexOf("\\");
        //                                Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                fieldName = fieldName.Trim();

        //                                if (ColumnName + (i + 1).ToString() == fieldName)
        //                                {
        //                                    myMergeField.Select();
        //                                    application.Selection.TypeText(strValue);
        //                                }
        //                            }
        //                        }
        //                    }

        //                    var Dic0 = Dics[0];

        //                    foreach (var DicItem in Dic)
        //                    {
        //                        var ColumnName = DicItem.Key;
        //                        //document.ReplaceText("«" + ColumnName + "»", "");
        //                        foreach (Field myMergeField in wordDoc.Fields)
        //                        {
        //                            Range rngFieldCode = myMergeField.Code;
        //                            String fieldText = rngFieldCode.Text;

        //                            if (fieldText.StartsWith(" MERGEFIELD"))
        //                            {
        //                                Int32 endMerge = fieldText.IndexOf("\\");
        //                                Int32 fieldNameLength = fieldText.Length - endMerge;
        //                                String fieldName = fieldText.Substring(11, endMerge - 11);
        //                                fieldName = fieldName.Trim();

        //                                if (fieldName.Contains(ColumnName))
        //                                {
        //                                    myMergeField.Select();

        //                                    if (application.Selection.Text == "«" + fieldName + "»")
        //                                        application.Selection.TypeText(" ");
        //                                }
        //                            }
        //                        }
        //                    }
        //                }
        //            }

        //            if (areas != null)
        //            {
        //                foreach (var area in areas)
        //                {
        //                    object oEndOfDoc = area.Key;
        //                    bool bShow = area.Value;
        //                    Range wrdRng = wordDoc.Bookmarks[ref oEndOfDoc].Range;//.get_Item(ref oEndOfDoc).Range;
        //                    if (!bShow)
        //                    {
        //                        wrdRng.Delete();
        //                    }
        //                }
        //            }

        //            foreach (Field myMergeField in wordDoc.Fields)
        //            {
        //                Range rngFieldCode = myMergeField.Code;
        //                String fieldText = rngFieldCode.Text;

        //                if (fieldText.StartsWith(" MERGEFIELD"))
        //                {
        //                    Int32 endMerge = fieldText.IndexOf("\\");
        //                    Int32 fieldNameLength = fieldText.Length - endMerge;
        //                    String fieldName = fieldText.Substring(11, endMerge - 11);
        //                    fieldName = fieldName.Trim();
        //                    myMergeField.Select();

        //                    if (application.Selection.Text == "«" + fieldName + "»")
        //                        application.Selection.TypeText(".................");
        //                }
        //            }

        //            //document.SaveAs(strSaveAsFilePath);
        //            wordDoc.SaveAs(strSaveAsFilePath);
        //            application.Application.Quit();
        //            bOK = true;
        //        }
        //        catch (Exception ex)
        //        {
        //            throw ex;
        //        }
        //        finally
        //        {
        //            //wordDoc.Close();
        //            application.Application.Quit();
        //        }
        //    }

        //    return bOK;
        //}

        public void CreateBRTable(string strTemplateFilePath, int row,string saveTemp)
        {
            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                for (int i = 0; i < row; i++)
                {
                    Row rx = document.Tables[1].InsertRow(document.Tables[1].RowCount);
                    
                    rx.Cells[0].Paragraphs[0].Append("«" + "bgm_exp_name" + (i + 1).ToString() + "»");
                    rx.Cells[0].Paragraphs[0].FontSize(16);
                    rx.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    rx.Cells[1].Paragraphs[0].Append("«" + "remarks" + (i + 1).ToString() + "»");
                    rx.Cells[1].Paragraphs[0].FontSize(16);
                    rx.Cells[1].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    rx.Cells[2].Paragraphs[0].Append("«" + "amount" + (i + 1).ToString() + "»");
                    rx.Cells[2].Paragraphs[0].Alignment = Alignment.right;
                    rx.Cells[2].Paragraphs[0].FontSize(16);
                    rx.Cells[2].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                }

                Row sum = document.Tables[1].InsertRow(document.Tables[1].RowCount);
                sum.MergeCells(0, 1);
                //หลังการเมิจภายในฟิลด์จะเกิดช่องว่างหากใส่ข้อมูลเลยจะทำให้ช่องกว้างกว่าปกติ จึงต้องทำการเคีลยร์ก่อน
                int paraCount1 = sum.Cells[0].Paragraphs.Count;

                for (int k = 1; k < paraCount1; k++)
                {
                    sum.Cells[0].Paragraphs[0].Remove(false);
                }

                sum.Cells[0].Paragraphs[0].Append("รวมเป็นเงินทั้งสิ้น");
                sum.Cells[0].Paragraphs[0].Alignment = Alignment.right;
                sum.Cells[0].Paragraphs[0].FontSize(16);
                sum.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                sum.Cells[1].Paragraphs[0].Append("«" + "AmountMoney" + "»");
                sum.Cells[1].Paragraphs[0].Alignment = Alignment.right;
                sum.Cells[1].Paragraphs[0].FontSize(16);
                sum.Cells[1].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                Row sumText = document.Tables[1].InsertRow(document.Tables[1].RowCount);
                sumText.MergeCells(0, 2);
                //หลังการเมิจภายในฟิลด์จะเกิดช่องว่างหากใส่ข้อมูลเลยจะทำให้ช่องกว้างกว่าปกติ จึงต้องทำการเคีลยร์ก่อน
                int paraCount2 = sumText.Cells[0].Paragraphs.Count;

                for (int l = 1; l < paraCount2; l++)
                {
                    sumText.Cells[0].Paragraphs[0].Remove(false);
                }
                sumText.Cells[0].Paragraphs[0].Append("("+"«" + "MoneyTextThai" + "»"+")");
                sumText.Cells[0].Paragraphs[0].Alignment = Alignment.right;
                sumText.Cells[0].Paragraphs[0].FontSize(16);
                sumText.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                document.SaveAs(saveTemp);
            }
        }

        public void CreateCLSTable(string strTemplateFilePath, int row, string saveTemp)
        {
            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                for (int i = 0; i < row; i++)
                {
                    Row rx = document.Tables[0].InsertRow(document.Tables[0].RowCount);

                    rx.Cells[0].Paragraphs[0].Append("«" + "bgm_exp_name" + (i + 1).ToString() + "»");
                    rx.Cells[0].Paragraphs[0].FontSize(16);
                    rx.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    rx.Cells[1].Paragraphs[0].Append("«" + "remarks" + (i + 1).ToString() + "»");
                    rx.Cells[1].Paragraphs[0].FontSize(16);
                    rx.Cells[1].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                    rx.Cells[2].Paragraphs[0].Append("«" + "amount" + (i + 1).ToString() + "»");
                    rx.Cells[2].Paragraphs[0].Alignment = Alignment.right;
                    rx.Cells[2].Paragraphs[0].FontSize(16);
                    rx.Cells[2].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                }

                Row sum = document.Tables[0].InsertRow(document.Tables[0].RowCount);
                sum.MergeCells(0, 1);
                //หลังการเมิจภายในฟิลด์จะเกิดช่องว่างหากใส่ข้อมูลเลยจะทำให้ช่องกว้างกว่าปกติ จึงต้องทำการเคีลยร์ก่อน
                int paraCount1 = sum.Cells[0].Paragraphs.Count;

                for (int k = 1; k < paraCount1; k++)
                {
                    sum.Cells[0].Paragraphs[0].Remove(false);
                }

                sum.Cells[0].Paragraphs[0].Append("รวมเป็นเงินทั้งสิ้น");
                sum.Cells[0].Paragraphs[0].Alignment = Alignment.right;
                sum.Cells[0].Paragraphs[0].FontSize(16);
                sum.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));
                sum.Cells[1].Paragraphs[0].Append("«" + "SumAmountRtn" + "»");
                sum.Cells[1].Paragraphs[0].Alignment = Alignment.right;
                sum.Cells[1].Paragraphs[0].FontSize(16);
                sum.Cells[1].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                Row sumText = document.Tables[0].InsertRow(document.Tables[0].RowCount);
                sumText.MergeCells(0, 2);
                //หลังการเมิจภายในฟิลด์จะเกิดช่องว่างหากใส่ข้อมูลเลยจะทำให้ช่องกว้างกว่าปกติ จึงต้องทำการเคีลยร์ก่อน
                int paraCount2 = sumText.Cells[0].Paragraphs.Count;

                for (int l = 1; l < paraCount2; l++)
                {
                    sumText.Cells[0].Paragraphs[0].Remove(false);
                }
                sumText.Cells[0].Paragraphs[0].Append("(" + "«" + "MoneyTextThai" + "»" + ")");
                sumText.Cells[0].Paragraphs[0].Alignment = Alignment.right;
                sumText.Cells[0].Paragraphs[0].FontSize(16);
                sumText.Cells[0].Paragraphs[0].Font(new FontFamily("TH SarabunPSK"));

                document.SaveAs(saveTemp);
            }
        }
  
        public bool MergeTemplate(string strTemplateFilePath, string strSaveAsFilePath
            , Dictionary<string, string> fields
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , List<Dictionary<string, string>> Dics = null
            , Dictionary<string, bool> areas = null)
        {
            bool bOK = false;

            using (DocX document = DocX.Load(strTemplateFilePath))
            {
                try
                {
                    if (fields != null)
                    {
                        foreach (var field in fields)
                        {
                            document.ReplaceText("«" + field.Key + "»", field.Value);
                        }
                    }

                    if (dtbs != null)
                    {
                        foreach (var _dtb in dtbs)
                        {
                            System.Data.DataTable dtb = _dtb.Value;
                            for (int i = 0; i < dtb.Rows.Count; i++)
                            {
                                System.Data.DataRow dr = dtb.Rows[i];

                                foreach (System.Data.DataColumn col in dtb.Columns)
                                {
                                    var ColumnName = col.ColumnName + (i + 1).ToString();
                                    string strValue = dr[col.ColumnName].ToString();
                                    double dValue = 0;

                                    if (double.TryParse(strValue, out dValue))
                                    {
                                        strValue = dValue.ToString("#,##0.00");
                                    }

                                    document.ReplaceText("«" + ColumnName + "»", strValue);
                                }
                            }
                        }
                    }

                    if (Dics != null && Dics.Count > 0)
                    {
                        for (int i = 0; i < Dics.Count; i++)
                        {
                            var Dic = Dics[i];

                            foreach (var DicItem in Dic)
                            {
                                string ColumnName = DicItem.Key + (i + 1).ToString();
                                string strValue = DicItem.Value;

                                document.ReplaceText("«" + ColumnName + "»", strValue);
                            }
                        }
                    }

                    if (areas != null)
                    {
                    }

                    while (document.Text.IndexOf("«") >= 0)
                    {
                        int Left = document.Text.IndexOf("«");
                        int Right = document.Text.IndexOf("»");

                        string strText = document.Text.Substring(Left, Right - Left + 1);
                        document.ReplaceText(strText, "");
                    }

                    document.SaveAs(strSaveAsFilePath);
                    bOK = true;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                }
            }

            return bOK;
        }

        public void SaveFileToClient(HttpResponse response, string strFilePath, string strFileName, bool bDeleteFile)
        {
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8));
            response.WriteFile(strFilePath + strFileName);
            response.Flush();
            if (bDeleteFile)
            {
                System.IO.File.Delete(strFilePath + strFileName);
            }
            response.End();
        }

        public string MergeString(string strValue, string strIfEmpty = "...................")
        {
            return string.IsNullOrEmpty(strValue) ? strIfEmpty : strValue;
        }

        public string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        public bool MergeExcel(HttpResponse response, string strTemplateFilePath, string strFileName
            , Dictionary<string, System.Data.DataTable> dtbs = null
            , Dictionary<string, string> Dics = null
            , OfficeOpenXml.Table.TableStyles tableStyle = OfficeOpenXml.Table.TableStyles.None)
        {
            bool bOK = false;

            FileInfo file = new FileInfo(strTemplateFilePath);

            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                if (pck.Workbook.Worksheets.Count > 0)
                {
                    ExcelWorksheet ws = pck.Workbook.Worksheets[1];

                    if (dtbs != null)
                    {
                        foreach (var dtb in dtbs)
                        {
                            if (dtb.Value.Rows.Count > 0)
                            {
                                ws.Cells[dtb.Key].LoadFromDataTable(dtb.Value, false);
                            }
                        }
                    }

                    if (Dics != null)
                    {
                        foreach (var Dic in Dics)
                        {
                            ws.Cells[Dic.Key].Value = Dic.Value;
                        }
                    }

                    response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    response.AddHeader("content-disposition", string.Format("attachment;  filename={0}.xlsx", HttpUtility.UrlEncode(strFileName, System.Text.Encoding.UTF8)));
                    response.BinaryWrite(pck.GetAsByteArray());
                    pck.Dispose();
                }
            }
            return bOK;
        }

        public string ThaiBaht(string txt)
        {
            string bahtTxt, n, bahtTH = "";
            double amount;
            try { amount = Convert.ToDouble(txt); }
            catch { amount = 0; }
            bahtTxt = amount.ToString("####.00");
            string[] num = { "ศูนย์", "หนึ่ง", "สอง", "สาม", "สี่", "ห้า", "หก", "เจ็ด", "แปด", "เก้า", "สิบ" };
            string[] rank = { "", "สิบ", "ร้อย", "พัน", "หมื่น", "แสน", "ล้าน" };
            string[] temp = bahtTxt.Split('.');
            string intVal = temp[0];
            string decVal = temp[1];
            if (Convert.ToDouble(bahtTxt) == 0)
                bahtTH = "ศูนย์บาทถ้วน";
            else
            {
                for (int i = 0; i < intVal.Length; i++)
                {
                    n = intVal.Substring(i, 1);
                    if (n != "0")
                    {
                        if ((i == (intVal.Length - 1)) && (n == "1"))
                            bahtTH += "เอ็ด";
                        else if ((i == (intVal.Length - 2)) && (n == "2"))
                            bahtTH += "ยี่";
                        else if ((i == (intVal.Length - 2)) && (n == "1"))
                            bahtTH += "";
                        else
                            bahtTH += num[Convert.ToInt32(n)];
                        bahtTH += rank[(intVal.Length - i) - 1];
                    }
                }
                bahtTH += "บาท";
                if (decVal == "00")
                    bahtTH += "ถ้วน";
                else
                {
                    for (int i = 0; i < decVal.Length; i++)
                    {
                        n = decVal.Substring(i, 1);
                        if (n != "0")
                        {
                            if ((i == decVal.Length - 1) && (n == "1"))
                                bahtTH += "เอ็ด";
                            else if ((i == (decVal.Length - 2)) && (n == "2"))
                                bahtTH += "ยี่";
                            else if ((i == (decVal.Length - 2)) && (n == "1"))
                                bahtTH += "";
                            else
                                bahtTH += num[Convert.ToInt32(n)];
                            bahtTH += rank[(decVal.Length - i) - 1];
                        }
                    }
                    bahtTH += "สตางค์";
                }
            }
            return bahtTH;
        }
    }
}